/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.database;

import br.com.models.Teacher;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * Classe que impplemente a conexão e realiza
 * as operações CRUD com a base de dados referente
 * a classe Teacher.
 * 
 * @author Valdigleis
 */
public class DaoTeacher {
 
    private String sql;
    
    private Connection connection;

    /**
     * Método construtor que implementa a classe
     * DaoTeacher, que é responsavel pela comunicação
     * do sistema com a base de dados, para objetos
     * da classe Teacher.
     *  
     */
    public DaoTeacher() {
        this.connection = Derby.getConnection();
    }
    
    /**
     * Método responsável por grava as informação do
     * objeto Teacher na tabela APP.TEACHER da base
     * de dados.
     * 
     * @param teacher
     * @return true ou false
     */
    public void save(Teacher teacher){
        try{
            sql = "INSERT INTO SAD.TEACHER" +
                " (name, register)" + " values (?,?)";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, teacher.getName());
            stmt.setString(2, teacher.getRegister());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao salvar o professor!");
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Método responsavel por captura o id do ultimo professor na base.
     * @return id
     */
    public int idFinalTeacher(){
        try{
            sql =  "SELECT MAX(id) as id FROM SAD.TEACHER";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            ResultSet rs = stmt.executeQuery(); 
            rs.next(); 
            int lastId = rs.getInt("id"); 
            rs.close(); 
            stmt.close();
            return lastId; 
        }catch(SQLException e){
            System.err.println("Erro ao peagar o id do ultimo professor!");
            System.out.println(e.getMessage());
            return -1;
        }
    }
    
    /**
     * Método que captura todos os professores na base de dados.
     * 
     * @return teachers
     */
    public ArrayList<Teacher> seacherAllTeacher(){
        try{
            ArrayList<Teacher>  teachers = new ArrayList<Teacher>();
            sql =  "SELECT * FROM SAD.TEACHER";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                Teacher teacher = new Teacher();
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("name"));
                teacher.setRegister(rs.getString("register"));
                teachers.add(teacher);
            } 
            stmt.close();
            return teachers;
        }catch(SQLException e){
            System.err.println("Erro ao fazer a busca dos professores na base!");
            System.out.println(e.getMessage());
            return null;
        }
    }
    
    public Teacher seacherTeacherById(int Id){
        try{
            sql =  "SELECT * FROM SAD.TEACHER WHERE id=?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
            stmt.setInt(1, Id);
            ResultSet rs = stmt.executeQuery(); 
            Teacher teacher = new Teacher();
            while(rs.next()){
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("name"));
                teacher.setRegister(rs.getString("register"));
            } 
            stmt.close();
            return teacher;
        }catch(SQLException e){
            System.err.println("Erro ao fazer a busca dos professores na base!");
            System.out.println(e.getMessage());
            return null;
        }
    }
    
    public Teacher seacherTeacher(int idTeacher){
        try{
            sql =  "SELECT * FROM SAD.TEACHER WHERE id=?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
            stmt.setInt(1, idTeacher);
            ResultSet rs = stmt.executeQuery(); 
            Teacher teacher = new Teacher();
            while(rs.next()){
                teacher.setId(rs.getInt("id"));
                teacher.setName(rs.getString("name"));
                teacher.setRegister(rs.getString("register"));
            } 
            rs.close();
            stmt.close();
            return teacher;
        }catch(SQLException e){
            System.err.println("Erro ao fazer a busca do professores na base!");
            System.out.println(e.getMessage());
            return null;
        }
    }
    
    /**
     * Método que atualização as informações do professor
     * na base de dados.
     * 
     * @param teacher 
     */
    public void update(Teacher teacher){
        try{
            sql = "UPDATE SAD.TEACHER set name=?, register=? WHERE id=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, teacher.getName());
            stmt.setString(2, teacher.getRegister());
            stmt.setInt(3, teacher.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao atualizar o professor!");
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Método que analisa se uma determinada matricula já está em uso pelo 
     * professor.     * 
     * @param registerTeacher
     * @return true ou false
     */
    public boolean checkRegister(String registerTeacher){
        try{
            sql = "SELECT * FROM SAD.TEACHER WHERE register=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, registerTeacher);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                closeConnection();
                return true;
            }
        }catch(SQLException e){
            System.err.println("Erro ao verifar existência do register na base de dados!");
            System.out.println(e.getMessage());
        }
        closeConnection();
        return false;
    }
    
    public void remove(Teacher teacher){
        try{
            sql = "DELETE FROM SAD.TEACHER WHERE id=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, teacher.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.err.println("Erro ao verifar existência do register na base de dados!");
            System.out.println(e.getMessage());
        }
    }
    /**
     * Método que encerar a conexão com o banco.
     */
    public void closeConnection(){
        try{
            this.connection.close();
        }catch(SQLException e){
            System.err.println("Erro tentar fechar a conexão do professor!");
            System.out.println(e.getMessage());
        }
    }
}
